package com.mouse.remind;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.io.StreamCorruptedException;
import java.util.ArrayList;
import java.util.List;

import com.ze.familydayverpm.userinfo.UserInfoManager;


import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/* 
 * usage:  
 * DatabaseSetup.init(egActivityOrContext); 
 * DatabaseSetup.createEntry() or DatabaseSetup.getContactNames() or DatabaseSetup.getDb() 
 * DatabaseSetup.deactivate() then job done 
 */

public class Database extends SQLiteOpenHelper {
	static Database instance = null;
	static SQLiteDatabase database = null;
	
	static final String DATABASE_NAME = "DB";
	static final int DATABASE_VERSION = 1;
	
	public static String ALARM_TABLE = "alarm";
	public static final String COLUMN_ALARM_ID = "_id";
	public static final String COLUMN_ALARM_ALARMID = "alarm_id";
	public static final String COLUMN_ALARM_ACTIVE = "alarm_active";	
	public static final String COLUMN_ALARM_VIBRATE = "alarm_vibrate";
	//public static final String COLUMN_ALARM_TIME = "alarm_time";
	public static final String COLUMN_ALARM_SUBJECT = "alarm_subject";
	public static final String COLUMN_ALARM_YEAR = "alarm_year";
	public static final String COLUMN_ALARM_MONTH = "alarm_month";
	public static final String COLUMN_ALARM_DAY = "alarm_day";
	public static final String COLUMN_ALARM_WDAY = "alarm_wday";
	public static final String COLUMN_ALARM_TIME = "alarm_time";
	public static final String COLUMN_ALARM_UID = "alarm_uid";
	public static final String COLUMN_ALARM_TOUIDS = "alarm_touids";
	public static final String COLUMN_ALARM_REPEATDAY = "alarm_repeatday";
	public static final String COLUMN_ALARM_IDTYPE = "alarm_idtype";
	public static final String COLUMN_ALARM_USERS = "alarm_users";
	
		
	
	public static void init(Context context) {
		if (null == instance) {
			//ALARM_TABLE = "alarm_"+UserInfo.getInstance(context).getUid();
			if (UserInfoManager.getInstance(context).getItem("uid") != null) {
				ALARM_TABLE = "alarm" + UserInfoManager.getInstance(context).getItem("uid").getValue();
			}else {
				ALARM_TABLE = "alarm" + "23a";
			}
			
			instance = new Database(context);
			createTable();
		}
	}

	public static SQLiteDatabase getDatabase() {
		if (null == database && instance != null) {
			database = instance.getWritableDatabase();
		}
		return database;
	}

	public static void deactivate() {
		if (null != database && database.isOpen()) {
			database.close();
		}
		database = null;
		instance = null;
	}

	public static long create(AlarmModel alarm) {
		ContentValues cv = new ContentValues();
		cv.put(COLUMN_ALARM_ALARMID, alarm.getAlarmid());
		cv.put(COLUMN_ALARM_ACTIVE, alarm.getActive());
		cv.put(COLUMN_ALARM_VIBRATE, alarm.getVibrate());
		cv.put(COLUMN_ALARM_SUBJECT, alarm.getSubject());
		cv.put(COLUMN_ALARM_YEAR, alarm.getYear());
		cv.put(COLUMN_ALARM_MONTH, alarm.getMonth());
		cv.put(COLUMN_ALARM_DAY, alarm.getDay());
		cv.put(COLUMN_ALARM_WDAY, alarm.getWday());
		cv.put(COLUMN_ALARM_TIME, alarm.getTime());
		cv.put(COLUMN_ALARM_UID, alarm.getUid());
		cv.put(COLUMN_ALARM_TOUIDS, alarm.getTouids());
		cv.put(COLUMN_ALARM_REPEATDAY, alarm.getRepeatday());
		cv.put(COLUMN_ALARM_IDTYPE, alarm.getIdtype());
		
		try {
			ByteArrayOutputStream bos = new ByteArrayOutputStream();
		    ObjectOutputStream oos = null;
		    oos = new ObjectOutputStream(bos);
		    oos.writeObject(alarm.getUserList());
		    byte[] buff = bos.toByteArray();
		    
		    cv.put(COLUMN_ALARM_USERS, buff);
		    
		} catch (Exception e){
		}		
		
		return getDatabase().insert(ALARM_TABLE, null, cv);
	}
	
	public static boolean createAll(List<AlarmModel> alarms) {
		boolean success = false;
		/*long start =System.currentTimeMillis();*/
		
		SQLiteDatabase db = getDatabase();
		db.beginTransaction();
	    try {
	    	for (AlarmModel alarm : alarms) {
				if (!Database.isAlarmExist(alarm.getAlarmid())) {
					Log.i(Database.class.getSimpleName(),"create(alarm)");
					Database.create(alarm);
					success = true;
				}
			}
	      db.setTransactionSuccessful();
	    } catch (Exception e) {
	    	success = false;
	    	e.printStackTrace();
		}finally {
	      db.endTransaction();
	    }
		
		/*long end = System.currentTimeMillis();
		System.out.println("all:"+(end-start));*/
		
		return success;
	}
	public static int update(AlarmModel alarm) {
		ContentValues cv = new ContentValues();
		cv.put(COLUMN_ALARM_ALARMID, alarm.getAlarmid());
		cv.put(COLUMN_ALARM_ACTIVE, alarm.getActive());
		cv.put(COLUMN_ALARM_VIBRATE, alarm.getVibrate());
		cv.put(COLUMN_ALARM_SUBJECT, alarm.getSubject());
		cv.put(COLUMN_ALARM_YEAR, alarm.getYear());
		cv.put(COLUMN_ALARM_MONTH, alarm.getMonth());
		cv.put(COLUMN_ALARM_DAY, alarm.getDay());
		cv.put(COLUMN_ALARM_WDAY, alarm.getWday());
		cv.put(COLUMN_ALARM_TIME, alarm.getTime());
		cv.put(COLUMN_ALARM_UID, alarm.getUid());
		cv.put(COLUMN_ALARM_TOUIDS, alarm.getTouids());
		cv.put(COLUMN_ALARM_REPEATDAY, alarm.getRepeatday());
		cv.put(COLUMN_ALARM_IDTYPE, alarm.getIdtype());
		
		try {
			ByteArrayOutputStream bos = new ByteArrayOutputStream();
		    ObjectOutputStream oos = null;
		    oos = new ObjectOutputStream(bos);
		    oos.writeObject(alarm.getUserList());
		    byte[] buff = bos.toByteArray();
		    
		    cv.put(COLUMN_ALARM_USERS, buff);
		    
		} catch (Exception e){
		}
					
		return getDatabase().update(ALARM_TABLE, cv, COLUMN_ALARM_ID + "=" + alarm.getId(), null);
	}
	public static int deleteEntry(AlarmModel alarm){
		if (alarm.getId() < 1) {
			return deleteEntryByAlarmid(alarm.getAlarmid());
		}else {
			return deleteEntry(alarm.getId());
		}
	}
	
	public static int deleteEntry(int id){
		return getDatabase().delete(ALARM_TABLE, COLUMN_ALARM_ID + "=" + id, null);
	}
	
	public static int deleteEntryByAlarmid(String alarmid){
		return getDatabase().delete(ALARM_TABLE, COLUMN_ALARM_ALARMID + "=" + alarmid, null);
	}
	
	public static int deleteAll(){
		return getDatabase().delete(ALARM_TABLE, "1", null);
	}
	
	public static AlarmModel getAlarm(int id) {
		// TODO Auto-generated method stub
		String[] columns = new String[] { 
				COLUMN_ALARM_ID,
				COLUMN_ALARM_ALARMID,
				COLUMN_ALARM_ACTIVE,	
				COLUMN_ALARM_VIBRATE,
				COLUMN_ALARM_SUBJECT,
				COLUMN_ALARM_YEAR,
				COLUMN_ALARM_MONTH,
				COLUMN_ALARM_DAY,
				COLUMN_ALARM_WDAY,
				COLUMN_ALARM_TIME,
				COLUMN_ALARM_UID,
				COLUMN_ALARM_TOUIDS,
				COLUMN_ALARM_REPEATDAY,
				COLUMN_ALARM_IDTYPE,
				COLUMN_ALARM_USERS
				};
		Cursor cursor = getDatabase().query(ALARM_TABLE, columns, COLUMN_ALARM_ID+"="+id, null, null, null,
				null);
		AlarmModel alarm = null;
		
		if(cursor.moveToFirst()){
			
			alarm = new AlarmModel();
			alarm.setId(cursor.getInt(0));
			alarm.setAlarmid(cursor.getString(1));
			alarm.setActive(cursor.getInt(2) == 1);
			alarm.setVibrate(cursor.getInt(3) == 1);
			alarm.setSubject(cursor.getString(4));
			alarm.setYear(cursor.getString(5));
			alarm.setMonth(cursor.getString(6));
			alarm.setDay(cursor.getString(7));
			alarm.setWday(cursor.getString(8));
			alarm.setTime(cursor.getString(9));
			alarm.setUid(cursor.getString(10));
			alarm.setTouids(cursor.getString(11));
			alarm.setRepeatday(cursor.getString(12));
			alarm.setIdtype(cursor.getString(13));
			
			
			byte[] usersBytes = cursor.getBlob(14);

			ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(
					usersBytes);
			try {
				ObjectInputStream objectInputStream = new ObjectInputStream(
						byteArrayInputStream);
				ArrayList<User> userList;
				Object object = objectInputStream.readObject();
				if (object instanceof ArrayList<?>) {
					userList = (ArrayList<User>) object;
					alarm.setUserList(userList);
				}
			} catch (StreamCorruptedException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}
		}
		cursor.close();
		return alarm;
	}
	
	public static boolean isAlarmExist(String alarmid) {
		// TODO Auto-generated method stub
		boolean isExist = false;
		String[] columns = new String[] { 
				COLUMN_ALARM_ID,
				COLUMN_ALARM_ALARMID/*,
				COLUMN_ALARM_ACTIVE,	
				COLUMN_ALARM_VIBRATE,
				COLUMN_ALARM_SUBJECT,
				COLUMN_ALARM_YEAR,
				COLUMN_ALARM_MONTH,
				COLUMN_ALARM_DAY,
				COLUMN_ALARM_WDAY,
				COLUMN_ALARM_TIME,
				COLUMN_ALARM_UID,
				COLUMN_ALARM_TOUIDS,
				COLUMN_ALARM_REPEATDAY,
				COLUMN_ALARM_IDTYPE,
				COLUMN_ALARM_USERS*/
				};
		Cursor cursor = getDatabase().query(ALARM_TABLE, columns, COLUMN_ALARM_ALARMID+"="+alarmid, null, null, null,
				null);
		
		if(cursor.moveToFirst()){
			isExist = true;
		}
		cursor.close();
		return isExist;
	}
	
	public static Cursor getCursor() {
		// TODO Auto-generated method stub
		String[] columns = new String[] { 
				COLUMN_ALARM_ID,
				COLUMN_ALARM_ALARMID,
				COLUMN_ALARM_ACTIVE,	
				COLUMN_ALARM_VIBRATE,
				COLUMN_ALARM_SUBJECT,
				COLUMN_ALARM_YEAR,
				COLUMN_ALARM_MONTH,
				COLUMN_ALARM_DAY,
				COLUMN_ALARM_WDAY,
				COLUMN_ALARM_TIME,
				COLUMN_ALARM_UID,
				COLUMN_ALARM_TOUIDS,
				COLUMN_ALARM_REPEATDAY,
				COLUMN_ALARM_IDTYPE,
				COLUMN_ALARM_USERS
				};
		return getDatabase().query(ALARM_TABLE, columns, null, null, null, null,
				null);
	}

	Database(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL("CREATE TABLE IF NOT EXISTS " + ALARM_TABLE + " ( " 
				+ COLUMN_ALARM_ID + " INTEGER primary key autoincrement, " 
				+ COLUMN_ALARM_ALARMID + " TEXT NOT NULL, "
				+ COLUMN_ALARM_ACTIVE + " INTEGER NOT NULL, " 
				+ COLUMN_ALARM_VIBRATE + " INTEGER NOT NULL, " 
				+ COLUMN_ALARM_SUBJECT + " TEXT NOT NULL, " 
				+ COLUMN_ALARM_YEAR + " TEXT NOT NULL, " 
				+ COLUMN_ALARM_MONTH + " TEXT NOT NULL, " 
				+ COLUMN_ALARM_DAY + " TEXT NOT NULL, " 
				+ COLUMN_ALARM_WDAY + " TEXT NOT NULL, " 
				+ COLUMN_ALARM_TIME + " TEXT NOT NULL, " 
				+ COLUMN_ALARM_UID + " TEXT NOT NULL, " 
				+ COLUMN_ALARM_TOUIDS + " TEXT NOT NULL, " 
				+ COLUMN_ALARM_REPEATDAY + " TEXT NOT NULL, " 
				+ COLUMN_ALARM_IDTYPE + " TEXT NOT NULL, " 
				+ COLUMN_ALARM_USERS + " BLOB NOT NULL) ");
	}
	
	public static void createTable() {
		if (instance == null) {
			return;
		}
		instance.onCreate(getDatabase());
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + ALARM_TABLE);
		onCreate(db);
	}

	public static List<AlarmModel> getAll() {
		List<AlarmModel> alarms = new ArrayList<AlarmModel>();
		Cursor cursor = Database.getCursor();
		if (cursor.moveToFirst()) {

			do {
				/*COLUMN_ALARM_ID,
				COLUMN_ALARM_ALARMID,
				COLUMN_ALARM_ACTIVE,	
				COLUMN_ALARM_VIBRATE,
				COLUMN_ALARM_SUBJECT,
				COLUMN_ALARM_YEAR,
				COLUMN_ALARM_MONTH,
				COLUMN_ALARM_DAY,
				COLUMN_ALARM_WDAY,
				COLUMN_ALARM_TIME,
				COLUMN_ALARM_UID,
				COLUMN_ALARM_TOUIDS,
				COLUMN_ALARM_REPEATDAY,
				COLUMN_ALARM_IDTYPE,
				COLUMN_ALARM_USERS*/

				AlarmModel alarm = new AlarmModel();
				alarm.setId(cursor.getInt(0));
				alarm.setAlarmid(cursor.getString(1));
				alarm.setActive(cursor.getInt(2) == 1);
				alarm.setVibrate(cursor.getInt(3) == 1);
				alarm.setSubject(cursor.getString(4));
				alarm.setYear(cursor.getString(5));
				alarm.setMonth(cursor.getString(6));
				alarm.setDay(cursor.getString(7));
				alarm.setWday(cursor.getString(8));
				alarm.setTime(cursor.getString(9));
				alarm.setUid(cursor.getString(10));
				alarm.setTouids(cursor.getString(11));
				alarm.setRepeatday(cursor.getString(12));
				alarm.setIdtype(cursor.getString(13));
				
				
				byte[] usersBytes = cursor.getBlob(14);

				ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(
						usersBytes);
				try {
					ObjectInputStream objectInputStream = new ObjectInputStream(
							byteArrayInputStream);
					ArrayList<User> userList;
					Object object = objectInputStream.readObject();
					if (object instanceof ArrayList<?>) {
						userList = (ArrayList<User>) object;
						alarm.setUserList(userList);
					}
				} catch (StreamCorruptedException e) {
					e.printStackTrace();
				} catch (IOException e) {
					e.printStackTrace();
				} catch (ClassNotFoundException e) {
					e.printStackTrace();
				}
				
				alarms.add(alarm);

			} while (cursor.moveToNext());			
		}
		cursor.close();
		return alarms;
	}
}